package io.chance.receiverlei.db;

import io.chance.receiverlei.db.model.BdMoRtSingle;
import lrq.common.MyLog;
import lrq.data.DataTable;
import org.apache.log4j.Logger;

import java.sql.Timestamp;
import java.util.Calendar;
import java.util.UUID;

public class DbHelper {
    private static Logger log = MyLog.getLogger(DbHelper.class.getSimpleName());

    public static void createOrUpdateSingle(BdMoRtSingle single) {
        String sql = "select * from bd_mo_rt_single t where t.SITE_ID='" + single.getSITE_ID() + "' and t.PARAMETER='" + single.getPARAMETER() + "' order by DATATIME desc";
        try {
            DataTable table = DbManager.getDbExecuter().executeQuery(sql);
            Timestamp now = new Timestamp(Calendar.getInstance().getTime().getTime());
            if (table.getRecordCount() == 0) {//没有记录，新建
                single.setID(UUID.randomUUID().toString()).setDATATIME(now).setCREATETIME(now);
                log.info(single.getSITE_ID() + "新增参数：" + single.getPARAMETER() + ",值为：" + single.getVALUE());
                DbManager.getDbExecuter().Create(single);
            }
            else {//single表为1个，正常
                String id = table.getString(0, "ID");
                single.setID(id).setDATATIME(now);
                log.info(single.getSITE_ID() + "更新参数：" + single.getPARAMETER() + ",值为：" + single.getVALUE());
                DbManager.getDbExecuter().update(single);//必须更新，至少是datatime
                if (table.getRecordCount() > 1) {
                    log.info(single.getSITE_ID() + "站点single不正常,准备清除" + table.getRecordCount());
                    sql = "delete from bd_mo_rt_single where SITE_ID='" + single.getSITE_ID() + "' and PARAMETER='" + single.getPARAMETER() + "' and ID !=" +
                        "(select c.ID from (select ID from bd_mo_rt_single where SITE_ID='" + single.getSITE_ID() + "' and PARAMETER='" + single.getPARAMETER() + "' order by DATATIME desc limit 1)c)";
                    DbManager.getDbExecuter().executeUpdate(sql);
                }
            }
        }
        catch (Exception e) {
            log.error("createOrUpdateSingle", e);
        }
    }

    //初始化当前月数据表1,1,2
    public static void createDataTableIfNotExist(String tableName) {
        String sql = "select * from information_schema.tables where table_name ='" + tableName + "'";
        try {
            DataTable table = DbManager.getDbExecuter().executeQuery(sql);
            if (table.getRecordCount() == 0) {//表不存在
                sql = "CREATE TABLE `" + tableName + "` (\n" +
                    "  `ITEMID` VARCHAR(36) NOT NULL,\n" +
                    "  `InstPos` VARCHAR(50) ,\n" +
                    "  `GATEWAY` VARCHAR(36) NOT NULL,\n" +
                    "  `Address` VARCHAR(50) NOT NULL DEFAULT '1',\n" +
                    "  `DEVICETYPE` INT(11) NOT NULL,\n" +
                    "  `OnLineFlag` TINYINT(1) NOT NULL DEFAULT 0,\n" +
                    "  `CreateTime` DATETIME NULL DEFAULT now(),\n" +
                    "  `AXDL` DOUBLE NULL,\n" +
                    "  `BXDL` DOUBLE NULL,\n" +
                    "  `CXDL` DOUBLE NULL,\n" +
                    "  `AZXDL` DOUBLE NULL,\n" +
                    "  `BZXDL` DOUBLE NULL,\n" +
                    "  `CZXDL` DOUBLE NULL,\n" +
                    "  `AIRSWITCH` INT(11) NULL,\n" +
                    "  `SPDJD` INT(11) NULL,\n" +
                    "  `SPD` INT(11) NULL,\n" +
                    "  `SpdNum` INT(11) NULL,\n" +
                    "  `LJNUM` INT(11) NULL,\n" +
                    "  `AXDY` DOUBLE NULL,\n" +
                    "  `BXDY` DOUBLE NULL,\n" +
                    "  `CXDY` DOUBLE NULL,\n" +
                    "  `TEMPERATURE1` DOUBLE NULL,\n" +
                    "  `HUMIDITY1` DOUBLE NULL,\n" +
                    "  `TEMPERATURE2` DOUBLE NULL,\n" +
                    "  `Humidity2` DOUBLE NULL,\n" +
                    "  `ARXDL` DOUBLE NULL,\n" +
                    "  `BRXDL` DOUBLE NULL,\n" +
                    "  `CRXDL` DOUBLE NULL,\n" +
                    "  `LJTIME` DATETIME ,\n" +
                    "  PRIMARY KEY (`ITEMID`),\n" +
                    "  UNIQUE INDEX `ItemId_UNIQUE` (`ITEMID` ASC))\n" +
                    "ENGINE = InnoDB\n" +
                    "DEFAULT CHARACTER SET = utf8\n" +
                    "COLLATE = utf8_general_ci;";
//                    "COLLATE = utf8_unicode_ci;";
                DbManager.getDbExecuter().executeUpdate(sql);
                log.info(tableName + "新建成功");
            }
        }
        catch (Exception e) {
            log.error("createDataTableIfNotExist", e);
        }
    }
}
